﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using libDAL;
using System.Data;
using System.Windows.Forms;

namespace libInventory.BUS
{

     public sealed  class clsBUSAdditionalServices : IDisposable 
    {


        private cls_DAL _DAL;
        private DataSet dsAdditionalServicesPaidBy;
        private DataSet dsAdditionalServicesHeader;
        private DataRow[] drHeaderDetails;
        private DataRow[] drOrigin;
        private DataRow[] drDestination;
        private DataRow drNewHeader;
        private DataTable dtPaidHeaders;

        private string strTablenames;
        private string strGoodReceivedNote = string.Empty;
        private string strConsigneeID = string.Empty;
        private string strShipperID = string.Empty;
        private string str3rdParty = string.Empty;
        private string strAdditionalServicesHeaderID = string.Empty;
        private string strSalesTermHeaderID = string.Empty;
        private string strAdditionalServicesDescription = string.Empty;

        public string getset_AdditionalServicesDescription
        {
            get
            {
                return strAdditionalServicesDescription;
            }
            set
            {
                strAdditionalServicesDescription = value;
            }
        }

        public string getset_SalesTermHeaderID
        {
            get
            {
               return strSalesTermHeaderID;
            }
            set
            {
                strSalesTermHeaderID = value;
            }
        }

        public string getset_AdditionalServicesHeaderID
        {
            get
            {
                return this.strAdditionalServicesHeaderID;
            }
            set
            {
                this.strAdditionalServicesHeaderID = value;
            }
        }

        public string getset_ConsigneeID
        {
            get
            {
                return strConsigneeID;
            }
            set
            {
                strConsigneeID = value;
            }
        }

        public string getset_ShipperID
        {
            get
            {
                return strShipperID;
            }
            set
            {
                strShipperID = value;
            }
        }

        private string strTempConStrgrn = @"Data Source=193.9.200.92\SERVERARCHIE;Initial Catalog=ESB_FMSTransactions;User ID=abc;Password=1234";
       
        private struct Queries
        {
            public string get_QueryAdditionalServicesSetsDetails(string strPaidHeadersID, string strShipperID, 
                                                          string strConsigneeID, string str3rdParty) 
            {
                string strQuery = string.Empty;
                try
                {
                    if (strPaidHeadersID != string.Empty & ((strShipperID != string.Empty && strConsigneeID != string.Empty) || str3rdParty != string.Empty))
                    {

                        strQuery = "SELECT SalesTermAdditionalPaidDetailsID, SalesTermAdditionalPaidGroup,SalesTermAdditionalPaidHeadersID, " +
                                   "SalesTermSetupDetailsID, Rated,RateM3, MinRateM3, MinAmountM3, RateWeight, MinRateWeight, " +
	                               "MinAmountWeight, RateVolume, MinRateVolume, MinAmountVolume, RateQuantity, MinRateQuantity, MinAmountQuantity, " +
                                   "InvoicePercentage, ChargeType,PerShipment, ExpiryFrom, ExpiryTo, GoodReceivedNote, comid, ChargePaidBy, InLandSetupChargesID, " +
                                   " CodeDescription, DateTimeInserted, DateTimeUpdated, LastModifiedBy, HostNameIPAddress, OnUse, OnUseBy " +
                                    "FROM SalesTermAdditionalPaidDetails " +
                                   "WHERE SalesTermAdditionalPaidHeadersID = '" + strPaidHeadersID + "'";
                    }
                    else
                    {
                        strQuery = "SELECT top(0) SalesTermAdditionalPaidDetailsID, SalesTermAdditionalPaidGroup,SalesTermAdditionalPaidHeadersID, " +
                                   "SalesTermSetupDetailsID, Rated,RateM3, MinRateM3, MinAmountM3, RateWeight, MinRateWeight, " +
                                   "MinAmountWeight, RateVolume, MinRateVolume, MinAmountVolume, RateQuantity, MinRateQuantity, MinAmountQuantity, " +
                                   "InvoicePercentage, ChargeType,PerShipment, ExpiryFrom, ExpiryTo, GoodReceivedNote, comid, ChargePaidBy, InLandSetupChargesID, " +
                                   " CodeDescription, DateTimeInserted, DateTimeUpdated, LastModifiedBy, HostNameIPAddress, OnUse, OnUseBy  " +
                                   "FROM SalesTermAdditionalPaidDetails";
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + " get_QueryAdditionalServicesSets"); 
                }
                return strQuery;
            }

            public string get_QueryAdditionalServicesHeaders(string strSalesTermAdditionalPaidHeadersID)
            {
                string strQuery = string.Empty;
                try
                {
                    if (strSalesTermAdditionalPaidHeadersID != string.Empty)
                    {
                        strQuery = "SELECT SalesTermAdditionalPaidHeadersID, SalesTermSetupHeadersID, AdditionalServicesDescription, " +
                                   "GoodReceivedNote, ShipperID, ConsigneeID, ThirdParyID, DateTimeInserted, DateTimeUpdated, " +
                                   "LastModifiedBy, HostNameIPAddress, OnUse, OnUseBy, ExpiryFrom, ExpiryTo, SalesTermID " +
                                   "FROM SalesTermAdditionalPaidHeaders " +
                                   "WHERE SalesTermAdditionalPaidHeadersID = '" + strSalesTermAdditionalPaidHeadersID + "'";
                    }
                    else
                    {
                        strQuery = "SELECT top(0) SalesTermAdditionalPaidHeadersID, SalesTermSetupHeadersID, AdditionalServicesDescription, " +
                                   "GoodReceivedNote, ShipperID, ConsigneeID, ThirdParyID, DateTimeInserted, DateTimeUpdated, " +
                                   "LastModifiedBy, HostNameIPAddress, OnUse, OnUseBy, ExpiryFrom, ExpiryTo, SalesTermID " +
                                   "FROM SalesTermAdditionalPaidHeaders ";
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + " get_QueryAdditionalServicesDetails"); 
                }
                return strQuery;
            }

            public string get_QueryAdditionalServicesSetsHeader(string strShipperID, string strConsigneeID)
            {
                return "SELECT SalesTermAdditionalPaidHeadersID as [Transaction ID], AdditionalServicesDescription as [Description(s)/Remark(s)], " +
                       "ShipperID as [Shipper ID], ConsigneeID as [Consignee ID], ThirdParyID as [ThirdPary ID] " +
                       "FROM SalesTermAdditionalPaidHeaders " +
                       "WHERE ShipperID = '" + strShipperID + "' and ConsigneeID = '" + strConsigneeID + "' " +
                       "GROUP BY SalesTermAdditionalPaidHeadersID, AdditionalServicesDescription, ShipperID, ConsigneeID, ThirdParyID";
            }
        }
         

        Queries Query;

        public cls_DAL get_DAL
        {
            get
            {
                return _DAL;
            }
        }

        private void set_addNewTableName()
        { 
            //Array.Resize(ref this.strTablenames, intIndex + 1);
            //strTablenames[this.strTablenames.Count] = sqlDa;
            //bolStatus = true;
        }
         
        public clsBUSAdditionalServices()
        {
            try
            {
                dtPaidHeaders = new DataTable();
                strTablenames = string.Empty;
                _DAL = new cls_DAL(this.strTempConStrgrn);
                Query = new Queries();
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " clsBUSAdditionalServices"); 
            }
        }

        public string getset_GoodReceivedNote
        {
            get
            {
                return this.strGoodReceivedNote;
            }
            set
            {
                strGoodReceivedNote = value;
            }
        }

        public DataRow[] getset_DestinationAditionalCharges
        {
            get
            {
                return this.drDestination;
            }
            set
            {
                this.drDestination = value;
            }
        }

        public DataRow[] getset_OriginAditionalCharges
        {
            get
            {
                return this.drOrigin;
            }
            set
            {
                this.drOrigin = value;
            }
        }

        public DataTable get_PaidHeaders
        {
            get
            {
                return this.dtPaidHeaders;
            }
        }

         public string getset_3rdParty
        {
            get
            {
                return str3rdParty;
            }
            set
            {
                str3rdParty = value;
            }
        }
         
        public bool set_PaidHeaders()
        {
            bool bolStatus = false;
            try
            {
                if (getset_ConsigneeID.Trim() != string.Empty && getset_ShipperID.Trim() != string.Empty)
                {
                    this.dtPaidHeaders = _DAL.get_TableDetail(Query.get_QueryAdditionalServicesSetsHeader(getset_ShipperID, getset_ConsigneeID));
                }
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_PaidHeaders");
            }

            return bolStatus;
        }

        public bool set_LoadAdditionalServicesHeader(string strPaidHeadersID)
        {
            bool bolStatus = false;
            try
            {
                dsAdditionalServicesHeader = new DataSet();
                if (_DAL.set_DataWCardinality_ds(Query.get_QueryAdditionalServicesHeaders(strPaidHeadersID),
                                                 ref dsAdditionalServicesHeader, "AdditionalHeader-1") == true)
                {
                    drHeaderDetails = dsAdditionalServicesHeader.Tables["AdditionalHeader-1"].Select(); 
                    bolStatus = true;
                }
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_LoadAdditionalServicesHeader"); 
            }

            return bolStatus;
        }
        public bool set_InsertUpdateAdditionalServices(DataTable dtSource)
        {
            bool bolStatus = false;
            try
            {

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_InsertUpdateAdditionalServices"); 
            }
            return bolStatus; 
        }

        public bool set_LoadAdditionalServices(string strPaidHeadersID)
        {
            bool bolStatus = false;
            try
            {
                dsAdditionalServicesPaidBy = new DataSet(); 
                if (_DAL.set_DataWCardinality_ds(Query.get_QueryAdditionalServicesSetsDetails(strPaidHeadersID, getset_ShipperID, getset_ConsigneeID,getset_3rdParty), 
                                                 ref dsAdditionalServicesPaidBy, "AdditionalServices-0") == true)
                {
                    this.drDestination = dsAdditionalServicesPaidBy.Tables["AdditionalServices-0"].Select("ChargeType = 'Destination'");
                    this.drOrigin = dsAdditionalServicesPaidBy.Tables["AdditionalServices-0"].Select("ChargeType = 'Origin'");
                    bolStatus = true;
                }
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_LoadAdditionalServices"); 
            }

            return bolStatus;
        }


        public void Dispose()
        {
            _DAL.Close_Connection();
            GC.Collect();
        }


        public bool set_set_CommitUpdateSaveAdditionalServices()
        {
            bool bolStatus = false;
            try
            {
                if (_DAL.set_updateBatch(ref this.dsAdditionalServicesHeader, "AdditionalHeader-1") == true)
                {
                    if (_DAL.set_updateBatch(ref this.dsAdditionalServicesPaidBy, "AdditionalServices-0") == true)
                    {
                        bolStatus = true;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_set_CommitUpdateSaveAdditionalServices"); 
            }
            return bolStatus;
        }

        internal bool set_CommitUpdateSaveAdditionalServices()
        {
           return _DAL.set_updateBatch(ref this.dsAdditionalServicesPaidBy, "AdditionalServices-0"); 
        }

        internal DataRow[] get_OriginAdditionalCharges()
        {
           return dsAdditionalServicesPaidBy.Tables["AdditionalServices-0"].Select("ChargeType = 'Origin'");
        }

        internal DataRow[] get_DestinationAdditionalCharges()
        {
           return dsAdditionalServicesPaidBy.Tables["AdditionalServices-0"].Select("ChargeType = 'Destination'");
        }

        public bool get_CheckHeaderAndDetailsLoaded()
        {
            bool bolStatus = false;
            try
            {
                bool bolHeader = false;
                bool bolDetails = false;

                if (this.dsAdditionalServicesHeader == null)
                {
                    bolHeader = set_LoadAdditionalServicesHeader(string.Empty);
                }
                else
                {
                    bolHeader = true;
                }

                if (this.dsAdditionalServicesPaidBy == null)
                {
                    bolDetails = set_LoadAdditionalServices(string.Empty);
                }
                else
                {
                    bolDetails = true;
                }

                if (bolHeader == true && bolDetails == true)
                {
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_CheckHeaderAndDetailsLoaded"); 
            }

            return bolStatus;
        }

        private DataTable get_Columns(DataTable dtSource)
        {
            DataTable dt = new DataTable();
            try
            {
                dt.Columns.Add ("column",typeof(string ));
                DataRow drInsert;
                for (int intCol = 0; intCol < dtSource.Columns.Count; intCol++)
                {
                    drInsert = dt.NewRow();
                    drInsert["column"] = dtSource.Columns[intCol].ColumnName;
                    dt.Rows.Add(drInsert);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_Columns"); 
            }

            return dt;
        }

        internal bool set_TransferToDataset(DataTable dtStorage)
        {
            bool bolStatus = false;
            try
            {
                DataRow drInsert;
                DataRow[] drStorage = dtStorage.Select();
                DataRow[] drcheck;
                DataTable dtcheck = dtStorage.Clone();
                DataTable dtColumns = get_Columns(dsAdditionalServicesPaidBy.Tables["AdditionalServices-0"]);
                for (int intRow = 0; intRow < drStorage.Length; intRow++)
                {

                    drInsert = dsAdditionalServicesPaidBy.Tables["AdditionalServices-0"].NewRow();
                    for (int intCol = 0; intCol < dtStorage.Columns.Count; intCol++)
                    {
                        drcheck = dtColumns.Select("column = '" + dtcheck.Columns[intCol].ColumnName + "'");
                        if (drcheck.Length > 0)
                        {
                            drInsert[dtcheck.Columns[intCol].ColumnName] = drStorage[intRow][dtcheck.Columns[intCol].ColumnName];
                        }
                    }
                    drInsert["ChargeType"] = drStorage[intRow]["ChargeType"];
                    dsAdditionalServicesPaidBy.Tables["AdditionalServices-0"].Rows.Add(drInsert);
                }

                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_TransferToDataset");  
            }
            return bolStatus;
        }

        public DataRow set_NewHeaderRow
        {
            get
            {
                return this.drNewHeader;
            }
            set
            {
                this.drNewHeader = value;
            }
        }

         

        internal bool set_CreateNewRowAdditionalServicesHeader()
        {
            bool bolStatus = false;
            try
            {
                drNewHeader = this.dsAdditionalServicesHeader.Tables["AdditionalHeader-1"].NewRow();

                //"SELECT top(0) SalesTermAdditionalPaidHeadersID, SalesTermSetupHeadersID, AdditionalServicesDescription, " +
                //                                  "GoodReceivedNote, ShipperID, ConsigneeID, ThirdParyID, DateTimeInserted, DateTimeUpdated, " +
                //                                  "LastModifiedBy, HostNameIPAddress, OnUse, OnUseBy, ExpiryFrom, ExpiryTo, SalesTermID " +
                //                                  "FROM SalesTermAdditionalPaidHeaders ";  

                drNewHeader["SalesTermAdditionalPaidHeadersID"] = getset_AdditionalServicesHeaderID;
                drNewHeader["SalesTermSetupHeadersID"] = getset_SalesTermHeaderID; //temporary getset_SalesTermHeaderIDA
                drNewHeader["AdditionalServicesDescription"] = getset_AdditionalServicesDescription; //temporary getset_SalesTermHeaderID
                drNewHeader["GoodReceivedNote"] = getset_GoodReceivedNote;
                drNewHeader["ShipperID"] = getset_ShipperID;
                drNewHeader["ConsigneeID"] = getset_ConsigneeID;
                drNewHeader["ThirdParyID"] = getset_3rdParty;

                this.dsAdditionalServicesHeader.Tables["AdditionalHeader-1"].Rows.Add(drNewHeader);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_CreateNewRowAdditionalServicesHeader"); 
            }
            return bolStatus;
        }



        internal bool set_CommitUpdateSaveAdditionalServicesHeader()
        {
            return _DAL.set_updateBatch(ref this.dsAdditionalServicesHeader, "AdditionalHeader-1");  
        }
    }
}
